package tw.com.softleader.reins.demo.exam.service;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import tw.com.softleader.reins.demo.exam.entity.Exam;

public class ExamService {
/* old methods
	static List<Test> tempTests=new ArrayList<Test>();
	public static void generateTests(){
		Test test=new Test();
		test.setId(1L);
		test.setName("Math");
		test.setDifficulty(1L);
		test.setItemLength(50L);
		test.setTime(90L);
		tempTests.add(test);
		
		test=new Test();
		test.setId(2L);
		test.setName("English");
		test.setDifficulty(3L);
		test.setItemLength(100L);
		test.setTime(70L);
		tempTests.add(test);
		
		test=new Test();
		test.setId(3L);
		test.setName("History");
		test.setDifficulty(1L);
		test.setItemLength(60L);
		test.setTime(60L);
		tempTests.add(test);
		
	}
	
	public List<Test> getAll(){
		List<Test> tests=new ArrayList<Test>();
		
		for (Test test : tempTests) {
			tests.add(test);
		}
		return tests;
	}
	
	public Test getById(Long id){

		for(Test test:tempTests){
			if(test.getId().equals(id)){
				return test;
			}
		}
		return null;
	}
	*/
	
	// new methods by using database
	public List<Exam> getAll() {
		List<Exam> exams=new ArrayList<Exam>();
		try {     
			Class.forName("org.postgresql.Driver");
			Connection conn = null;
			conn = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/softlink", "postgres", "postgres");
			String qryStmt = "SELECT * FROM test " ;
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(qryStmt);
			while(rs.next()) {
				Exam exam=new Exam();
				exam.setId(rs.getLong(1));
				exam.setName(rs.getString(2));
				exam.setDifficulty(rs.getLong(3));
				exam.setItemLength(rs.getLong(4));
				exam.setTime(rs.getLong(5));
				exams.add(exam);

			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return exams;
		
	}// end of getAll()
	
	public Exam getById(Long id) {
		try {     
			Class.forName("org.postgresql.Driver");
			Connection conn = null;
			conn = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/softlink", "postgres", "postgres");
			String qryStmt = "SELECT * FROM test " ;
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(qryStmt);
			

			while(rs.next()) {
				if(id.equals(rs.getLong(1))){
					Exam exam=new Exam();
					exam.setId(rs.getLong(1));
					exam.setName(rs.getString(2));
					exam.setDifficulty(rs.getLong(3));
					exam.setItemLength(rs.getLong(4));
					exam.setTime(rs.getLong(5));
					return exam;
				}
			}
			
			
			rs.close();
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
		
	}// end of getById()
	
//	public void insertData(int dataNum) {
//	
//		try {     
//			Class.forName("org.postgresql.Driver");
//			Connection conn = null;
//			conn = DriverManager.getConnection(
//					"jdbc:postgresql://localhost:5432/softlink", "postgres", "password");
//			String qryStmt = "SELECT max(id) FROM test " ;
//			Statement stmt = conn.createStatement();
//			ResultSet rs = stmt.executeQuery(qryStmt);
//			Long lastId;
//			while(rs.next()) {
//				lastId=(Long)rs.getLong(1)+1L;
//			}
//			String updateStmt = "INSERT INTO Test VALUES (?, ?, ?, ?,?);";
//			PreparedStatement pstmt = conn.prepareStatement(qryStmt);
//			pstmt = conn.prepareStatement(updateStmt);
//
//			for (int i=5 ; i < 6; i++) {
//    			
//				pstmt.setLong(1,i);
//				pstmt.setString(2,"i");
//				pstmt.setLong(3,i);
//				pstmt.setLong(4,i);
//				pstmt.setLong(5,i);
//				pstmt.addBatch();
//			}
//			
//			pstmt.executeBatch();
//			
//			pstmt.close();
//			rs.close();
//			stmt.close();
//			conn.close();
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		
//		
//	}// end of insertData()
}
